# This code is hosted on http://code.google.com/p/lenthorp/
# Freely available for use in applications, but should NOT be modified
# Email all comments to lenthorpresearch@gmail.com


import MySQLdb
from datetime import datetime

# Inserts data into selected database and table
#   dates must be an array of datetime objects
def InsertSQLDateRate(dates, rates, dbName, tableName, username='root'):
    db = MySQLdb.connect("localhost", username, '', dbName)
    c = db.cursor()
    c.execute('SHOW TABLES')
    tablesList = []
    for table in c.fetchall():
        tablesList.append(table[0].lower())
    if not tableName.lower() in tablesList:
        c.execute('CREATE TABLE `' + tableName + '` (	`Date` DATE NULL,`Last` FLOAT NULL);')
        c.execute('ALTER TABLE `' + tableName + '` ADD PRIMARY KEY (`Date`);')
    ##c.execute('USE `' + dbName + '`;')
    if not isinstance(dates, list):
        dates = [dates]
    if not isinstance(rates, list):
        rates = [rates]
    if len(dates) != len(rates):
        raise Exception('rates and dates must have same length')
    for idx in range(len(dates)):
        d = dates[idx]
        ##c.execute("SELECT * FROM " + tableName + " WHERE `Date` = '" + d.strftime('%Y-%m-%d') + "';")
        c.execute("REPLACE INTO `" + tableName +"` (`Date`, `Last`) VALUES ('" + d.strftime('%Y-%m-%d') + "'," + str(rates[idx]) + ");")
        ##c.execute("INSERT INTO `" + tableName +"` (`Date`, `Last`) SELECT * FROM dummy WHERE NOT EXISTS (SELECT * FROM " + tableName + " WHERE `Date` = '" + d.strftime('%Y-%m-%d') + "');")
    db.commit()
    ##c.execute('/*!40000 ALTER TABLE `' + tableName +'` ENABLE KEYS */;')
    db.close()